﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using OnLineExam.BLL;
using System.Data.OleDb;
using System.IO;
using System.Data.SqlClient;

namespace OnLineExam.Web.mag.old
{
    public partial class UserScore : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            //this.Page.Title = "成绩管理";
            if (!Page.IsPostBack)
            {

                string userId = User.Identity.Name;
                var loginuser = new UserManager().GetModel(userId);
                string userName = loginuser.UserName;
                

                var bll = new OnLineExam.BLL.PaperManager();

                this.DropDownList1.DataTextField = "PaperName";
                this.DropDownList1.DataValueField = "PaperID";
                this.DropDownList1.DataSource = bll.GetList("depid=" + loginuser.DepID);
                this.DropDownList1.DataBind();

                GridView1.DataSource = UserManager.selectAlls2(" and Score.depid=" + loginuser.DepID);
                GridView1.DataBind();
            }
        }
        protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            string id = GridView1.DataKeys[e.RowIndex].Value.ToString();

            string testuserid = e.Values[0].ToString();
            string testpaperid = e.Values[2].ToString();

            var cmlist = new List<HHG.DBUtility.CommandInfo>();
            cmlist.Add(new HHG.DBUtility.CommandInfo("delete from Score where id=@id", new SqlParameter[] { new SqlParameter("@id", id) }));
            cmlist.Add(new HHG.DBUtility.CommandInfo("delete from useranswer where userid=@userid and paperid=@paperid and scoreid=@scoreid", new SqlParameter[] { 
            new SqlParameter("@userid", testuserid),
            new SqlParameter("@paperid",testpaperid),
            new SqlParameter("@scoreid",id) }));
            HHG.DBUtility.DbHelperSQL.ExecuteSqlTranWithIndentity(cmlist);
            var loginuser = new UserManager().GetModel(User.Identity.Name);
            GridView1.DataSource = UserManager.selectAlls2(" and Score.depid=" + loginuser.DepID);
            GridView1.DataBind();
            Label12.Text = "删除成功！";
            ////cmlist.Add(new HHG.DBUtility.CommandInfo("delete from Score where id=@id", new SqlParameter[] { new SqlParameter("@id", id) }));

            //if (UserManager.DelScoreByID(id))//根据主键使用delScores方法删除用户
            //{
            //    HHG.DBUtility.DbHelperSQL.ExecuteSql("delect from useranswer where userid='"+testuserid+"' and paperid="+testpaperid);

            //    GridView1.DataSource = UserManager.selectAlls();
            //    GridView1.DataBind();
            //    Label12.Text = "删除成功！";
            //}
            //else
            //{
            //    Response.Write("<script language=javascript>alert('" + id + "删除失败!')</script>");
            //}

        }

        protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
        {
            //生成将要存放结果的Excel文件的名称
            string NewFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            //转换为物理路径
            NewFileName = Server.MapPath(NewFileName);
            //根据模板正式生成该Excel文件
            File.Copy(Server.MapPath("Module01.xls"), NewFileName, true);
            //建立指向该Excel文件的数据库连接
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + NewFileName + ";Extended Properties='Excel 8.0;'";
            OleDbConnection Conn = new OleDbConnection(strConn);
            //打开连接，为操作该文件做准备
            Conn.Open();
            OleDbCommand Cmd = new OleDbCommand("", Conn);

            //if (f)
            //{
            foreach (GridViewRow DR in GridView1.Rows)
            {
                int a = DR.Cells.Count;


                string XSqlString = "insert into [Sheet1$]";
                XSqlString += "([用户编号],[用户姓名],[试卷编号],[成绩],[考试时间]) values(";
                XSqlString += "'" + (DR.Cells[0].FindControl("lblUserID") as Label).Text + "',";
                XSqlString += "'" + (DR.Cells[1].FindControl("lblUserName") as Label).Text + "',";
                XSqlString += "'" + (DR.Cells[2].FindControl("Label11") as Label).Text + "',";
                XSqlString += "'" + (DR.Cells[3].FindControl("Label4") as Label).Text + "',";
                XSqlString += "'" + (DR.Cells[4].FindControl("Label5") as Label).Text + "')";
                Cmd.CommandText = XSqlString;
                Cmd.ExecuteNonQuery();
            }
            //}


            //操作结束，关闭连接
            Conn.Close();
            //打开要下载的文件，并把该文件存放在FileStream中
            System.IO.FileStream Reader = System.IO.File.OpenRead(NewFileName);
            //文件传送的剩余字节数：初始值为文件的总大小
            long Length = Reader.Length;

            Response.Buffer = false;
            Response.AddHeader("Connection", "Keep-Alive");
            Response.ContentType = "application/octet-stream";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("学生成绩.xls"));
            Response.AddHeader("Content-Length", Length.ToString());

            byte[] Buffer = new Byte[10000];		//存放欲发送数据的缓冲区
            int ByteToRead;											//每次实际读取的字节数

            while (Length > 0)
            {
                //剩余字节数不为零，继续传送
                if (Response.IsClientConnected)
                {
                    //客户端浏览器还打开着，继续传送
                    ByteToRead = Reader.Read(Buffer, 0, 10000);					//往缓冲区读入数据
                    Response.OutputStream.Write(Buffer, 0, ByteToRead);	//把缓冲区的数据写入客户端浏览器
                    Response.Flush();																		//立即写入客户端
                    Length -= ByteToRead;																//剩余字节数减少
                }
                else
                {
                    //客户端浏览器已经断开，阻止继续循环
                    Length = -1;
                }
            }

            //关闭该文件
            Reader.Close();
            //删除该Excel文件
            File.Delete(NewFileName);
        }
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string PaperID = DropDownList1.SelectedValue;
            GridView1.DataSource = UserManager.selectAllScore(PaperID);
            GridView1.DataBind();
        }

        protected void RadioButton1_CheckedChanged(object sender, EventArgs e)
        {
            for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
            {
                ((CheckBox)GridView1.Rows[i].FindControl("chkSelected")).Checked = RadioButton1.Checked;
            }
        }
        protected void RadioButton2_CheckedChanged(object sender, EventArgs e)
        {
            for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
            {
                if (((CheckBox)GridView1.Rows[i].FindControl("chkSelected")).Checked == true)
                {
                    ((CheckBox)GridView1.Rows[i].FindControl("chkSelected")).Checked = false;
                }
                else
                {
                    ((CheckBox)GridView1.Rows[i].FindControl("chkSelected")).Checked = true;
                }
            }
        }
        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                e.Row.Attributes.Add("onmouseover", "this.style.backgroundColor='#cbe2fa'");
                e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor='#FFFFFF'");
            }
        }
        protected void Button1_Click(object sender, EventArgs e)
        {

            string uainfo = "";
            for (int i = 0; i < this.GridView1.Rows.Count; i++)
            {
                bool isChecked = ((CheckBox)GridView1.Rows[i].FindControl("chkSelected")).Checked;
                if (isChecked)
                {
                    if (uainfo == "")
                    {
                        // selectid = GridView1.DataKeys[i][0].ToString();
                        uainfo = ((Label)GridView1.Rows[i].FindControl("lblUserID")).Text + "-" + ((Label)GridView1.Rows[i].FindControl("Label11")).Text +
                            "-" + GridView1.DataKeys[i][0].ToString();
                        //testpaperid = ((Label)GridView1.Rows[i].FindControl("Label11")).Text;
                    }
                    else
                    {
                        //selectid += "," + GridView1.DataKeys[i][0].ToString();
                        uainfo += "," + ((Label)GridView1.Rows[i].FindControl("lblUserID")).Text + "-" + ((Label)GridView1.Rows[i].FindControl("Label11")).Text
                            + "-" + GridView1.DataKeys[i][0].ToString();
                    }

                }
            }

            var cmlist = new System.Collections.Generic.List<HHG.DBUtility.CommandInfo>();

            foreach (string item in uainfo.Split(','))
            {
                if (!string.IsNullOrEmpty(item))
                {
                     
                    cmlist.Add(new HHG.DBUtility.CommandInfo("delete from useranswer where userid=@userid and paperid=@paperid and scoreid=@scoreid", new SqlParameter[] { 
                    new SqlParameter("@userid",item.Split('-')[0]),
                    new SqlParameter("@paperid",item.Split('-')[1]),
                     new SqlParameter("@scoreid",item.Split('-')[2])
                }));
                }
            }
            foreach (string item in uainfo.Split(','))
            {
                if (!string.IsNullOrEmpty(item))
                {
                    cmlist.Add(new HHG.DBUtility.CommandInfo("delete from Score where id=@id", new SqlParameter[] {
                    new SqlParameter("@id",item.Split('-')[2])
                }));
                    
                }
            }

            HHG.DBUtility.DbHelperSQL.ExecuteSqlTranWithIndentity(cmlist);
            var loginuser = new UserManager().GetModel(User.Identity.Name);
            GridView1.DataSource = UserManager.selectAlls2(" and Score.depid=" + loginuser.DepID);
            GridView1.DataBind();
            Label12.Text = "删除成功！";

        }
    }
}